package com.icss.daos;
/*
 * 论坛回贴dao类
 * 管志春
 * 2010.03.31
 * 
 * */
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.icss.beans.ReplyVO;
import com.icss.utils.DB;

public class ReplyDao {

	private PreparedStatement ps;

	private Connection conn = null;

	private ResultSet rs = null;

	private Statement stat = null;
	
	//获取所有回帖信息
	public ArrayList<ReplyVO> getAllReply() {

		ArrayList<ReplyVO> replyvos = new ArrayList<ReplyVO>();
		conn = DB.getConnection();

		String sql = "select * from Reply";

		try {
			stat = conn.createStatement();
			rs = stat.executeQuery(sql);

			while (rs.next()) {

				ReplyVO replyvo = new ReplyVO();
				replyvo.setRid(rs.getInt("rid"));
				replyvo.setReplyName(rs.getString("replyerName"));
				replyvo.setReplyBody(rs.getString("replyBody"));
				replyvo.setReplyTime(rs.getDate("replyTime"));

				replyvos.add(replyvo);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		close();
		return replyvos;

	}
	
	//根据文章id获取全部回帖信息
	public ArrayList<ReplyVO> getAllReplyByArticleID(int id) {

		ArrayList<ReplyVO> replyvos = new ArrayList<ReplyVO>();
		conn = DB.getConnection();
		String sql = "select * from Reply where aid=" + id;
		try {
			stat = conn.createStatement();
			rs = stat.executeQuery(sql);

			while (rs.next()) {

				ReplyVO replyvo = new ReplyVO();
				replyvo.setRid(rs.getInt("rid"));
				replyvo.setReplyName(rs.getString("replyerName"));
				replyvo.setReplyBody(rs.getString("replyBody"));
				replyvo.setReplyTime(rs.getDate("replyTime"));

				replyvos.add(replyvo);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		close();
		return replyvos;
	}
	
	//根据用户id获取去全部回帖信息
	public ArrayList<ReplyVO> getAllReplyByBid(int id) {

		ArrayList<ReplyVO> replyvos = new ArrayList<ReplyVO>();
		conn = DB.getConnection();
		String sql = "select * from Reply where bid=" + id;
		
		try {
			stat = conn.createStatement();
			rs = stat.executeQuery(sql);

			while (rs.next()) {

				ReplyVO replyvo = new ReplyVO();
				replyvo.setRid(rs.getInt("rid"));
				replyvo.setReplyName(rs.getString("replyerName"));
				replyvo.setReplyBody(rs.getString("replyBody"));
				replyvo.setReplyTime(rs.getDate("replyTime"));

				replyvos.add(replyvo);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		close();
		return replyvos;
	}

	//根据id获取回帖信息
	public ReplyVO getReplyByID(int replyID) {

		ReplyVO replyvo = new ReplyVO();
		conn = DB.getConnection();
		String sql = "select * from Reply where rid=" + replyID;
		try {
			stat = conn.createStatement();
			rs = stat.executeQuery(sql);

			while (rs.next()) {

				replyvo.setRid(rs.getInt("rid"));
				replyvo.setReplyName(rs.getString("replyerName"));
				replyvo.setReplyBody(rs.getString("replyBody"));
				replyvo.setReplyTime(rs.getDate("replyTime"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		close();
		return replyvo;

	}
	
	//获取回帖数量
	public int getAllReplyNum() {

		int result = 0;
		conn = DB.getConnection();
		String sql = "select count(*)as num  from Reply ";
		try {
			stat = conn.createStatement();
			rs = stat.executeQuery(sql);
			while(rs.next()){
				result = rs.getInt("num");
			}
		} catch (SQLException e) {
			close();
			e.printStackTrace();
		}

		close();
		return result;

	}
	
	//根据文章id获取回帖数
	public int getReplyNumByaid(int aid) {

		int result = 0;
		conn = DB.getConnection();
		String sql = "select count(*) as num from Reply where aid="+aid;
		try {
			stat = conn.createStatement();
			rs = stat.executeQuery(sql);
			while(rs.next()){
				result = rs.getInt("num");
			}
		} catch (SQLException e) {
			close();
			e.printStackTrace();
		}

		close();
		return result;

	}
	
	//执行SQL语句
	public boolean executeSQL(String sql) {

		int result = 0;
		conn = DB.getConnection();

		try {
			stat = conn.createStatement();
			result = stat.executeUpdate(sql);
		} catch (SQLException e) {
			close();
			e.printStackTrace();
		}

		if (result != 0) {
			return true;
		}
		close();
		return false;

	}

	public void close() {

		try {

			if (null != conn) {
				conn.close();
			}

			if (null != ps) {
				ps.close();
			}

			if (null != rs) {
				rs.close();
			}

			if (null != stat) {
				stat.close();
			}

		} catch (SQLException e) {
			e.printStackTrace();

		}
	}
}
